﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace BaoGiaVeThang.DAL
{
    public class DBHelper
    {
        private static string _cnnString = string.Empty;
        private static string getCnnString()
        {
            if (_cnnString == string.Empty)
            {
                _cnnString = ConfigurationManager.ConnectionStrings["DoAnTotNgiepDb"].ConnectionString;
            }
            return _cnnString;
        }
        #region[GetDataTableSP]
        public static DataTable GetDataTableSP(string spName, List<SqlParameter> listSqlParameter)
        {
            try
            {
                //FlyNowDbContext db = new FlyNowDbContext();
                DataSet dts = new DataSet();
                SqlConnection sqlConn = new SqlConnection(getCnnString());
                sqlConn.Open();
                SqlCommand command = new SqlCommand(spName, sqlConn);
                SqlDataAdapter daReport = new SqlDataAdapter(command);
                using (command)
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(listSqlParameter.ToArray());
                    daReport.Fill(dts);
                }
                sqlConn.Close();
                return dts.Tables[0];
            }
            catch
            {
                return null;
            }
        }
        public static DataTable GetDataTableSP(string spName)
        {
            try
            {

                DataSet dts = new DataSet();
                SqlConnection sqlConn = new SqlConnection(getCnnString());
                sqlConn.Open();
                SqlCommand command = new SqlCommand(spName, sqlConn);
                SqlDataAdapter daReport = new SqlDataAdapter(command);
                using (command)
                {
                    command.CommandType = CommandType.StoredProcedure;
                    daReport.Fill(dts);
                }
                sqlConn.Close();
                return dts.Tables[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion
        #region[ExecuteNonQuery]

        public static void ExecuteNonQuery(string SPName)
        {
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = new SqlConnection(getCnnString());
                sqlConn.Open();
                var sqlCommand = new SqlCommand(SPName, sqlConn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw (new Exception(ex.Message));
            }
            finally
            {
                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
            }
        }

        public static void ExecuteNonQuery(string SPName, List<SqlParameter> list)
        {
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = new SqlConnection(getCnnString());
                sqlConn.Open();
                var sqlCommand = new SqlCommand(SPName, sqlConn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param in list)
                {
                    sqlCommand.Parameters.Add(param);
                }
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw (new Exception(ex.Message));
            }
            finally
            {
                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
            }
        }
        #endregion
    }
}
